﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Linq.Expressions;
using System.Reflection;

namespace YTSK
{
    public static class CommandExt
    {

        public static List<T> Query<T>(this SqlCommand cmd, Expression<Func<T, bool>> whereExp)
        {
            var paras = new List<SqlParameter>();
            var sqlwhere = whereExp.GetSql(out paras);
            var tbname = typeof(T).Name;
            var sql = "select * from " + tbname + " where " + sqlwhere;
            var dt = cmd.GetData(sql, paras.ToArray());
            return dt.ToList<T>();
        }

        public static List<T> Query<T>(this SqlCommand cmd)
        {
            var tbname = typeof(T).Name;
            var sql = "select * from " + tbname;
            var dt = cmd.GetData(sql);
            return dt.ToList<T>();
        }
        public static T First<T>(this SqlCommand cmd, Expression<Func<T, bool>> whereExp)
        {
            var paras = new List<SqlParameter>();
            var sqlwhere = whereExp.GetSql(out paras);
            var tbname = typeof(T).Name;
            var sql = "select top 1 * from " + tbname + " where " + sqlwhere;
            var dt = cmd.GetData(sql, paras.ToArray());
            if (dt.Rows.Count > 0) { return dt.Rows[0].ToObj<T>(); }
            else { return default(T); }
        }

        public static T First<T>(this SqlCommand cmd)
        {
            var tbname = typeof(T).Name;
            var sql = "select top 1 * from " + tbname ;
            var dt = cmd.GetData(sql);
            if (dt.Rows.Count > 0) { return dt.Rows[0].ToObj<T>(); }
            else { return default(T); }
        }



        #region 获取SQL语句执行后得到的datatable
        /// <summary>
        /// 获取SQL语句执行后得到的datatable
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable GetData(this SqlCommand cmd, string sql, params SqlParameter[] paras)
        {
            DataTable dt = new DataTable();
            cmd.CommandText = sql;
            if (paras != null & paras.Count() > 0)
            {
                for (int i = 0; i < paras.Count(); i++)
                {
                    var p = paras[i];
                    if ( p.Value == null) { p.Value = DBNull.Value; }
                }
                cmd.Parameters.AddRange(paras);
            }
            dt.Load(cmd.ExecuteReader());
            cmd.Parameters.Clear();
            return dt;
        }
        #endregion

        #region 返回SQL语句的第一行第一列
        /// <summary>
        /// 返回SQL语句的第一行第一列
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static object GetObj(this SqlCommand cmd, string sql, params SqlParameter[] paras)
        {
            DataTable dt = new DataTable();
            cmd.CommandText = sql;
            if (paras != null & paras.Count() > 0)
            { cmd.Parameters.AddRange(paras); }
            var obj = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return obj;
        }
        #endregion

        #region 执行SQL语句
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExecSQL(this SqlCommand cmd, string sql, params SqlParameter[] paras)
        {
            cmd.CommandText = sql;
            int iRowEffect;
            if (paras != null & paras.Count() > 0)
            { cmd.Parameters.AddRange(paras); }
            iRowEffect = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return iRowEffect;
        }
        #endregion

        #region  把对象的数据写入到数据库
        /// <summary>
        /// 把对象的数据写入到数据库
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <param name="obj">待写入的对象</param>
        public static int InsertData(this SqlCommand cmd, object obj, string[] disableColums = null)
        {
            var tbname = obj.GetType().Name;
            var dtjud = GetTableColLength(tbname);
            cmd.CommandText = GetCmdText(tbname, obj, dtjud, disableColums) + Environment.NewLine + string.Format(" select IDENT_CURRENT('{0}')", tbname);
            GetCmdPara(obj, cmd, dtjud, disableColums, false);
            var r = cmd.ExecuteScalar().ObjToInt();
            cmd.Parameters.Clear();
            return r;
        }
        #endregion

        #region 生成插入数据的SQL语句
        /// <summary>
        /// 生成插入数据的SQL语句
        /// </summary>
        /// <param name="tbname">表名</param>
        /// <param name="obj">待插入的对象</param>
        /// <returns></returns>
        private static string GetCmdText(string tbname, object obj, DataTable dtrowsinsert, string[] disableColums = null)
        {
            string cmdsql = "";
            string cmdpara = "";
            Type type = obj.GetType();
            PropertyInfo[] ps = type.GetProperties();
            if (disableColums == null) { disableColums = new string[] { "" }; }
            foreach (PropertyInfo p in ps)
            {
                DataRow[] dtrsjud = dtrowsinsert.Select(string.Format("name='{0}'", p.Name));
                if (dtrsjud.Count() > 0)
                {
                    if (!disableColums.Contains(p.Name, new colnameCompare()))
                    {
                        object oval = p.GetValue(obj, null);
                        if (oval != null)
                        {
                            cmdsql += p.Name + ",";
                            cmdpara += "@" + p.Name + ",";
                        }
                    }
                }
            }
            cmdsql = cmdsql.Substring(0, cmdsql.Length - 1);
            cmdpara = cmdpara.Substring(0, cmdpara.Length - 1);
            cmdsql = "insert into  " + tbname + "( " + cmdsql + ") ";
            cmdsql += " values ( " + cmdpara + " ) ";
            return cmdsql;

        }
        #endregion

        #region  把对象集的数据写入到数据库
        /// <summary>
        /// 把对象集的数据写入到数据库
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <param name="obj">待写入的对象</param>
        public static void InsertDataList<T>(this SqlCommand cmd, List<T> obj, string[] disableColums = null)
        {
            for (int i = 0; i < obj.Count; i++)
            {
                var o = obj[i];
                InsertData(cmd, o, disableColums);
            }
        }
        #endregion


        #region 把对象更新到数据库
        /// <summary>
        /// 把对象更新到数据库
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <param name="obj">待写入的对象</param>
        public static void Update(this SqlCommand cmd, object obj, string priKey, string[] disableColumns = null)
        {
            var tbname = obj.GetType().Name;
            var dtjud = GetTableColLength(tbname);
            cmd.CommandText = GetCmdTextUpdate(tbname,dtjud, obj, priKey, disableColumns);
            GetCmdPara(obj, cmd, dtjud, disableColumns, true, priKey);
            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
        }
        #endregion



        #region 更新数据
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmd"></param>
        /// <param name="objval">待更新的列的匿名对象</param>
        /// <param name="whereExp">Where条件</param>
        public static int Update<T>(this SqlCommand cmd, object objval, Expression<Func<T, bool>> whereExp)
        {
            var paras = new List<SqlParameter>();
            var paras_value = new List<SqlParameter>();
            string sqlvalue = "";
            Type type = objval.GetType();
            var tbname = typeof(T).Name;
            PropertyInfo[] ps = type.GetProperties();
            foreach (var p in ps)
            {
                var oval = p.GetValue(objval, null);
                var para = UpdateObjValue(p.Name, oval);
                paras_value.Add(para);
                sqlvalue += string.Format("{0}=@{0},", p.Name);
            }
            sqlvalue = sqlvalue.Substring(0, sqlvalue.Length - 1);
            var sqlwhere = whereExp.GetSql(out paras);
            paras.AddRange(paras_value);
            var sql = "update " + tbname + " set " + sqlvalue + "  where " + sqlwhere;
            return cmd.ExecSQL(sql, paras.ToArray());
        }
        #endregion

        #region 更新数据
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmd"></param>
        /// <param name="objval">待更新的列的匿名对象</param>
        /// <param name="whereExp">Where条件</param>
        public static int Update<T>(this SqlCommand cmd, string updatesql, Expression<Func<T, bool>> whereExp)
        {
            var paras = new List<SqlParameter>();
            var tbname = typeof(T).Name;
            var sqlwhere = whereExp.GetSql(out paras);
            var sql = "update " + tbname + " set " + updatesql + "  where " + sqlwhere;
            return cmd.ExecSQL(sql, paras.ToArray());
        }
        #endregion

        #region 生成update值部分的SQL参数
        /// <summary>
        /// 生成update值部分的SQL参数
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private static SqlParameter UpdateObjValue(string name, object value)
        {
            string v_str = "@" + name;

            if (value == null)
            {
                var para = new SqlParameter(v_str, DBNull.Value);
                return para;
            }
            else
            {
                var para = new SqlParameter(v_str, value);
                return para;
            }
        }
        #endregion

        #region 获取表的nvarchar字段的长度，用于判断文本是否过长
        /// <summary>
        /// 获取表的nvarchar字段的长度，用于判断文本是否过长
        /// </summary>
        /// <param name="tbname">表名</param>
        /// <returns></returns>
        private static DataTable GetTableColLength(string tbname)
        {
            using (SqlConnection cn = new SqlConnection())
            {
                cn.Open();
                SqlCommand cmd = cn.CreateCommand();
                string sql = string.Format(@"select c.xtype,c.name,c.length from syscolumns c left join sysobjects o on c.id=o.id
                    where o.name = '{0}' and c.xtype<>189 and not (c.xtype=56 and c.typestat=1)", tbname);
                cmd.CommandText = sql;
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                cn.Close();
                return dt;
                // 231
            }
        }
        #endregion

        #region 生成更新数据的SQL语句
        /// <summary>
        /// 生成更新数据的SQL语句
        /// </summary>
        /// <param name="tbname">表名</param>
        /// <param name="obj">待更新的对象</param>
        /// <param name="priKey">主键</param>
        /// <returns></returns>
        private static string GetCmdTextUpdate(string tbname,DataTable dtjud, object obj, string priKey, string[] disableColums = null)
        {
            string cmdsql = "";
            Type type = obj.GetType();
            PropertyInfo[] ps = type.GetProperties();
            if (disableColums == null) { disableColums = new string[] { "" }; }
            foreach (PropertyInfo p in ps)
            {
                if (!disableColums.Contains(p.Name, new colnameCompare()))
                {
                    DataRow[] dtrsjud = dtjud.Select(string.Format("name='{0}'", p.Name));
                    if (dtrsjud.Count() > 0)
                    {
                        { cmdsql += string.Format("{0}=@{0},", p.Name); }
                    }
                }
            }
            cmdsql = cmdsql.Trim(',');
            cmdsql = "update  " + tbname + " set " + cmdsql;
            cmdsql += string.Format(" where {0}=@{0}", priKey);
            return cmdsql;

        }
        #endregion

        #region 生成导入的SQL参数值
        /// <summary>
        /// 生成导入的SQL参数值
        /// </summary>
        /// <param name="obj">待插入的对象</param>
        /// <param name="cncmd">SqlCommand对象</param>
        /// <param name="dt_jud">对象nvarchar字段长度</param>
        private static void GetCmdPara(object obj, SqlCommand cncmd, DataTable dt_jud, string[] disableColums = null, bool bUpdte = false, string priKey = "")
        {
            if (disableColums == null) { disableColums = new string[] { "" }; }
            cncmd.Parameters.Clear();
            Type type = obj.GetType();
            PropertyInfo[] ps = type.GetProperties();
            foreach (PropertyInfo p in ps)
            {
                object oval = p.GetValue(obj, null);
                SqlParameter sqlpa = new SqlParameter();
                sqlpa.ParameterName = p.Name;
                DataRow[] dtrsjud = dt_jud.Select(string.Format("name='{0}'", p.Name));
                if (dtrsjud.Count() > 0)
                {
                    if (!disableColums.Contains(p.Name, new colnameCompare()) || p.Name == priKey)
                    {
                        if (p.PropertyType != typeof(byte[]))
                        {                            
                            if (oval != null || bUpdte)
                            {
                                
                                DataRow[] dtrs = dt_jud.Select(string.Format("name='{0}' and xtype=231", p.Name));
                                if (dtrs.Length > 0)
                                {
                                    DataRow dtr = dtrs[0];
                                    int def_length = dtr["length"].ObjToInt();
                                    int length = oval.ObjToString().Length;
                                    if (length * 2 > def_length)
                                    {
                                        string errstr = string.Format("字段【{0}】的值【{1}】长度为{2}，已经超过定义的长度{3}，无法保存",
                                            p.Name, oval.ObjToString(), length, def_length);
                                        throw new Exception(errstr);
                                    }
                                }
                            }
                        }
                    }
                }
                if (oval != null)
                { sqlpa.Value = oval; }
                else
                { sqlpa.Value = DBNull.Value; }
                cncmd.Parameters.Add(sqlpa);
            }
        }
        #endregion

        #region 删除数据
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmd"></param>
        /// <param name="expression"></param>
        public static void Delete<T>(this SqlCommand cmd, Expression<Func<T, bool>> expression)
        {
            var tbname = typeof(T).Name;
            var paras = new List<SqlParameter>();
            var sqlwhere = expression.GetSql(out paras);
            var sql = "Delete from " + tbname + " where " + sqlwhere;
            cmd.ExecSQL(sql, paras.ToArray());
        }
        #endregion

    }

    #region 列名对比，忽略大小写
    /// <summary>
    /// 列名对比，忽略大小写
    /// </summary>
    public class colnameCompare : IEqualityComparer<string>
    {
        public bool Equals(string x, string y)
        {
            if (x.ToLower() == y.ToLower())
            { return true; }
            else
            { return false; }
        }

        public int GetHashCode(string obj)
        {
            throw new NotImplementedException();
        }
    }
    #endregion
}
